﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Web.Configuration;
using System.Text;
using Arbalest.Models;
using System.Web.Mvc;

namespace Arbalest.Web.Helpers
{
    public class DataHelper
    {
        public sealed class SqlServer
        {
            public static SqlServerDatabase COREPROD { get; set; }
            public static SqlServerDatabase RESEARCH_PROD_CORE { get; set; }
            public static SqlServerDatabase RESEARCH_PROD_VENDOR { get; set; }

            static SqlServer()
            {
                COREPROD = new SqlServerDatabase("DefaultConnection");
                RESEARCH_PROD_CORE = new SqlServerDatabase("RESEARCH_PROD_CORE");
                RESEARCH_PROD_VENDOR = new SqlServerDatabase("RESEARCH_PROD_VENDOR");
            }
        }

        #region GetData
        
        public static string GetData(object o)
        {
            return o.ToString();
        }

        public static string GetData(object o, string formatString)
        {
            return GetData(o, formatString, (IFormatProvider)null);
        }

        public static string GetData(object o, string formatString, string nullDisplayText)
        {
            if ((o == null) || (o == DBNull.Value))
                return nullDisplayText;

            return GetData(o, formatString, (IFormatProvider)null);
        }

        public static string GetData(object o, string formatString, IFormatProvider formatProvider)
        {
            IFormattable formattableObject = o as IFormattable;

            if (formattableObject != null)
                return formattableObject.ToString(formatString, formatProvider);

            return o.ToString();
        }

        public static string GetData(object o, string formatString, IFormatProvider formatProvider, string nullDisplayText)
        {
            if ((o == null) || (o == DBNull.Value))
                return nullDisplayText;

            return GetData(o, formatString, formatProvider);
        }

        #endregion GetData

        #region GetDataTypeCss
        
        public static string GetDataTypeCss(DataColumn o)
        {
            StringBuilder css = new StringBuilder();
            
            switch (Type.GetTypeCode(o.DataType))
            {
                case TypeCode.SByte:
                case TypeCode.Byte:
                case TypeCode.Int16:
                case TypeCode.UInt16:
                case TypeCode.Int32:
                case TypeCode.UInt32:
                case TypeCode.Int64:
                case TypeCode.UInt64:
                case TypeCode.Single:
                case TypeCode.Double:
                case TypeCode.Decimal:
                    css.Append(" numeric");
                    break;
                case TypeCode.String:
                    css.Append(" text");
                    break;
                case TypeCode.DateTime:
                    css.Append(" datetime");
                    break;
                case TypeCode.Boolean:
                case TypeCode.Char:
                case TypeCode.Empty:
                case TypeCode.Object:
                case TypeCode.DBNull:
                default:
                    break;
            }

            if (o.AllowDBNull)
                css.Append(" nullable");

            return css.ToString();
        }

        public static string GetDataTypeCss(object o)
        {
            

            StringBuilder css = new StringBuilder();
            
            if ((o == null) || (o == DBNull.Value))
                css.Append(" null");

            switch (Type.GetTypeCode(o.GetType()))
            {
                case TypeCode.SByte:
                case TypeCode.Byte:
                case TypeCode.Int16:
                case TypeCode.UInt16:
                case TypeCode.Int32:
                case TypeCode.UInt32:
                case TypeCode.Int64:
                case TypeCode.UInt64:
                case TypeCode.Single:
                case TypeCode.Double:
                case TypeCode.Decimal:
                    css.Append(" numeric");
                    break;
                case TypeCode.String:
                    css.Append(" text");
                    break;
                case TypeCode.DateTime:
                    css.Append(" datetime");
                    break;
                case TypeCode.Boolean:
                case TypeCode.Char:
                case TypeCode.Empty:
                case TypeCode.Object:
                case TypeCode.DBNull:
                default:
                    break;
                  
            }

            return css.ToString();
        }

        #endregion GetDataTypeCss

        #region MakeDictionary
        
        public static List<Dictionary<string, string>> MakeDictionary(DataTable dt)
        {
            List<Dictionary<string, string>> rowsList = new List<Dictionary<string, string>>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Dictionary<string, string> rec = new Dictionary<string, string>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    rec.Add(dt.Columns[j].ColumnName, dt.Rows[i][j].ToString());
                }

                rowsList.Add(rec);
            }

            return rowsList;
        }

        public static List<Dictionary<string, string>> MakeDictionary(DataRow[] dataRows)
        {
            List<Dictionary<string, string>> rowsList = new List<Dictionary<string, string>>();
            for (int i = 0; i < dataRows.Length; i++)
            {
                Dictionary<string, string> rec = new Dictionary<string, string>();
                for (int j = 0; j < dataRows[0].Table.Columns.Count; j++)
                {
                    rec.Add(dataRows[0].Table.Columns[j].ColumnName, dataRows[i][j].ToString());
                }

                rowsList.Add(rec);
            }

            return rowsList;
        }

        public static List<Dictionary<string, string>> MakeDictionary(DataRow dataRow)
        {
            List<Dictionary<string, string>> rowsList = new List<Dictionary<string, string>>();
            Dictionary<string, string> rec = new Dictionary<string, string>();
            for (int j = 0; j < dataRow.Table.Columns.Count; j++)
            {
                rec.Add(dataRow.Table.Columns[j].ColumnName, dataRow[j].ToString());
            }

            rowsList.Add(rec);
            return rowsList;
        }

        public static List<Dictionary<string, string>> MakeDictionary(List<DataRow> dataRows)
        {
            List<Dictionary<string, string>> rowsList = new List<Dictionary<string, string>>();
            for (int i = 0; i < dataRows.Count; i++)
            {
                Dictionary<string, string> rec = new Dictionary<string, string>();
                for (int j = 0; j < dataRows[0].Table.Columns.Count; j++)
                {
                    rec.Add(dataRows[0].Table.Columns[j].ColumnName, dataRows[i][j].ToString());
                }

                rowsList.Add(rec);
            }

            return rowsList;
        }

        #endregion MakeDictionary

        public sealed class TempData
        {
            public static string Alert = "Alert";
            public static string Exception = "Exception";

            #region Comment out
            
            // This block of code depicts trying to get tempdata (as if via webforms)
            // Code works in MVC2 

            //http://www.gregshackles.com/2010/07/asp-net-mvc-do-you-know-where-your-tempdata-is/
            //public static Alert Alert
            //{
            //    get;
            //    set;
            //}

            //private void TempDataWorkAround()
            //{
            //    //TempDataDictionary td = new TempDataDictionary();
            //    //SessionStateTempDataProvider tdProvider = new SessionStateTempDataProvider();
            //    //SessionStateTempDataProvider tdProvider = new SessionStateTempDataProvider(
            //new HttpContextWrapper(HttpContext.Current)
            //);
            //    //td["TEST"] = "as";
            //    //tdProvider.SaveTempData(td);
            //}
            #endregion Comment out
        }
    }

    // Defines all the functionality that we will need to read data from database
    public sealed class SqlServerDatabase
    {
        private string connectionString { get; set; }

        public SqlServerDatabase(string connectionStringName)
        {
            connectionString = WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Name of stored procedure</param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText)
        {
            return FetchDataSet(commandText, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Name of stored procedure</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText, params SqlParameter[] parameters)
        {
            //SqlParameterCollection parametersCollection = new SqlParameterCollection();
            //foreach (SqlParameter p in parameters)
            //{
            //    parametersCollection.Add(p);
            //}

            return FetchDataSet(commandText, CommandType.StoredProcedure, parameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">Dependent on commandType; Could be SQL statement, name of stored procedure or table name</param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public DataSet FetchDataSet(string commandText, CommandType commandType)
        {
            #region Idea to log stack

            // write call stack method names
            //System.Diagnostics.StackTrace stackTrace = new System.Diagnostics.StackTrace();           // get call stack
            //System.Diagnostics.StackFrame[] stackFrames = stackTrace.GetFrames();  // get method calls (frames)
            //foreach (System.Diagnostics.StackFrame stackFrame in stackFrames)
            //{
            //    Console.WriteLine(stackFrame.GetMethod().Name);   // write method name
            //}
            //stackFrames[1].GetMethod().Name
            #endregion Idea to log stack

            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                SaveDataAsXml(commandText, ds);

                return ds;
            }
        }

        public DataSet FetchDataSet(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                SaveDataAsXml(commandText, parameters, ds);

                return ds;
            }
        }

        public DataTable FetchSchema(string tableName)
        {
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM {0} WHERE 1 = 0", tableName);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.FillSchema(ds, SchemaType.Mapped);

                if ((ds != null) && (ds.Tables.Count > 0))
                {
                    ds.Tables[0].TableName = tableName;
                    return ds.Tables[0];
                }
            }

            return null;
        }

        #region Helper functions to save retrieved data as xml
        private void SaveDataAsXml(string commandText, DataSet ds)
        {
            //if ((ds == null) || (ds.Tables.Count <= 0))
            //    return;

            //string directoryPath = HttpContext.Current.Server.MapPath("~/App_Data/Site_Data/ResearchPortal/Xml");
            //string hashCode = HashHelper.MD5Hash(commandText);
            //string filePath = Path.Combine(directoryPath, hashCode);

            //filePath = Path.ChangeExtension(filePath, "xml");
            //if (!File.Exists(filePath))
            //    ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
            //else
            //    ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
        }

        private void SaveDataAsXml(string commandText, SqlParameter[] parameters, DataSet ds)
        {
            //foreach (SqlParameter p in parameters)
            //{
            //    commandText = commandText + string.Format(" {0}={1}", p.ParameterName, p.Value.ToString());
            //}

            //SaveDataAsXml(commandText, ds);
        }
        #endregion Helper functions to save retrieved data as xml

    }
}